#include "sql.h"

#include <unordered_map>

bool sql::connect(const QString &host, const qint32 &port,
                  const QString &username, const QString &pwd, const QString &db_name) {
    this->host = host;
    this->port = port;
    this->username = username;
    this->pwd = pwd;
    this->db_name = db_name;

    qDebug() << QSqlDatabase::drivers();

    qDebug() << QString::number((quint64)QThread::currentThreadId());
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL", QString::number((quint64)QThread::currentThreadId()));
    db.setHostName(host);
    db.setPort(port);
    db.setUserName(username);
    db.setPassword(pwd);
    db.setDatabaseName(db_name);
    if(db.open()){
        is_open_ = true;
        qDebug() << "OK" << "\n";
    }
    else{
        is_open_ = false;
        qDebug() << "Failed" << "\n";
    }
    return is_open_;
}

void sql::disconnect() {
    QString dbName = QString::number((quint64)QThread::currentThreadId());
    {
        QSqlDatabase dbConnected = QSqlDatabase::database(dbName);
        if (dbConnected.isValid())
        {
            dbConnected.close();
        }
    }
    QSqlDatabase::removeDatabase(dbName);
    qDebug() << "db disconnect";
}

sql::~sql() {
}


bool sql::insert_user(QString user, QString pwd) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    QString insert = "insert into user (username, pwd) values (:user, :pwd)";
    query.prepare(insert);
    query.bindValue(":user", user);
    query.bindValue(":pwd", pwd);
    bool res = query.exec();
    qDebug() << insert << " " << res << "\n";
    return res;
}

bool sql::insert_song(QString song_name, QString path) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    QString insert = "insert into song (song_name, path) values (:song_name, :path)";
    query.prepare(insert);
    query.bindValue(":song_name", song_name);
    query.bindValue(":path", path);
    bool res = query.exec();
    qDebug() << insert << " " << res << "\n";
    return res;
}

bool sql::insert_user_song(QString user, QString song_name) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    QString insert = "insert into user_song (username, song_name) values (:user, :song_name)";
    query.prepare(insert);
    query.bindValue(":user", user);
    query.bindValue(":song_name", song_name);
    bool res = query.exec();
    qDebug() << insert << " " << res << "\n";
    return res;
}

bool sql::insert_lrc(QString lrc_name, QString path) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    QString insert = "insert into lrc (lrc_name, path) values (:lrc_name, :path)";
    query.prepare(insert);
    query.bindValue(":lrc_name", lrc_name);
    query.bindValue(":path", path);
    bool res = query.exec();
    qDebug() << insert << " " << res << "\n";
    return res;
}

bool sql::insert_song_lrc(QString song_name, QString lrc_name) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    QString insert = "insert into song_lrc (song_name, lrc_name) values (:song_name, :lrc_name)";
    query.prepare(insert);
    query.bindValue(":song_name", song_name);
    query.bindValue(":lrc_name", lrc_name);
    bool res = query.exec();
    qDebug() << insert << " " << res << "\n";
    return res;
}

QString sql::select_lrc_path(QString song_name) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    query.prepare("select lrc.path from song_lrc,lrc where song_lrc.song_name = :song_name and song_lrc.lrc_name = lrc.lrc_name");
    query.bindValue(":song_name", song_name);
    query.exec();

    QString lrc_path;
    while(query.next()) {
        lrc_path = query.value(0).toString();
        break;
    }
    return lrc_path;
}

std::unordered_map<QString, QString> sql::select_song_name_and_path(QString user) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    query.prepare("select song.song_name, path from user_song,song where user_song.username = :user and user_song.song_name = song.song_name");
    query.bindValue(":user", user);
    query.exec();

    std::unordered_map<QString, QString> path_map;

    while(query.next()) {
        auto song_name = query.value(0).toString();
        auto path = query.value(1).toString();
        qDebug() << song_name << path;
        path_map[song_name] = path;
    }
    return path_map;
}

std::unordered_map<QString, QString> sql::select_song_lrc_and_path(QString user) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    query.prepare("select song.song_name, lrc.path from user_song,song,song_lrc,lrc where user_song.username = :user and user_song.song_name = song.song_name and song.song_name = song_lrc.song_name and song_lrc.lrc_name = lrc.lrc_name");
    query.bindValue(":user", user);
    query.exec();

    std::unordered_map<QString, QString> path_map;

    while(query.next()) {
        auto song_name = query.value(0).toString();
        auto path = query.value(1).toString();
        qDebug() << song_name << path;
        path_map[song_name] = path;
    }
    return path_map;
}

bool sql::select_user_and_pwd(QString user, QString pwd) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    query.prepare("select * from user where user.username = :user and user.pwd = :pwd");
    query.bindValue(":user", user);
    query.bindValue(":pwd", pwd);
    query.exec();

    bool is_success = false;
    while(query.next()) {
        is_success = true;
        qDebug() << query.value(0).toString() << query.value(1).toString();
        break;
    }
    return is_success;
}

bool sql::delete_song(QString song_name) {
    QSqlQuery query(QSqlDatabase::database(QString::number((quint64)QThread::currentThreadId())));
    QString sql_delete = "delete from song where song_name = :song_name";
    query.prepare(sql_delete);
    query.bindValue(":song_name", song_name);
    bool res = query.exec();
    qDebug() << sql_delete << " " << res << "\n";
    return res;
}

bool sql::is_open() const
{
    return is_open_;
}
